package pe.gob.servir.apps.censodirectivo.ejb.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import oracle.jdbc.OracleTypes;

import org.apache.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;

import pe.gob.servir.apps.censodirectivo.common.constantes.Constantes;
import pe.gob.servir.apps.censodirectivo.common.util.Utilitario;
import pe.gob.servir.apps.censodirectivo.excepcion.DAOExcepcion;
import pe.gob.servir.apps.censodirectivo.model.bean.DirectivoPublico;
import pe.gob.servir.apps.censodirectivo.model.bean.MensajeRespuesta;
import pe.gob.servir.apps.censodirectivo.model.bean.Reporte;
import pe.gob.servir.apps.censodirectivo.model.bean.Responsable;

/**
 * Session Bean implementation class ReporteDAO
 */
@Stateless
public class ReporteDAO implements ReporteDAOLocal {

	private static final Logger log = Logger.getLogger(ReporteDAO.class);
	
	@PersistenceContext(unitName = "persistenceUnitCenso")
	EntityManager entityManager;

	@EJB
	private ConsumirProcedimientoDAOLocal consumirProcedimientoDAOLocal;
	
    public ReporteDAO() {
       
    }

	@Override
	public List<Reporte> reporteNivelOrganizacionxClasificacion(final String entidadId,
			final String clasificacionEntidad,final String nivelOrganizacional,final String censoId) {
		
		Session session = entityManager.unwrap(Session.class);
		final List<Reporte> listaReporteSP = new ArrayList<Reporte>();
		session.doWork(new Work() {

			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				ResultSet resultset = null;

				try {

					callableStatement = connection.prepareCall(Constantes.SP_RPRT_NV_ORG_CLSFCCN_ENT);
					callableStatement.setString("E_ENTIDAD_ID", entidadId);
					callableStatement.setString("E_CLASIFICACION_ENTIDAD", clasificacionEntidad);
					callableStatement.setString("E_NIVEL_ORGANIZACIONAL", nivelOrganizacional);
					callableStatement.setString("E_CENSO_ID", censoId);
					callableStatement.registerOutParameter("S_CURSOR", OracleTypes.CURSOR);
					callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
					callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
					callableStatement.execute();
					log.info("Se ejecuto exitosamente el SP " + Constantes.SP_RPRT_NV_ORG_CLSFCCN_ENT);
					resultset = (ResultSet) callableStatement.getObject("S_CURSOR");//Cursor

					while (resultset.next()) {
						Reporte reporte = new Reporte();
						reporte.setEjeY(resultset.getString(1));
						reporte.setEjeX(resultset.getString(2));
						reporte.setCantidad(resultset.getLong(3));
						listaReporteSP.add(reporte);
					}

				} finally {
					Utilitario.cerrarConexiones(resultset, callableStatement);
				}
			}

		});

		return listaReporteSP;
	}
    
	@Override
	public List<Reporte> reporteTipoPuestoxClasificacion(final String entidadId,
			final String clasificacionEntidad,final String tipoPuesto,final String censoId) {
		
		Session session = entityManager.unwrap(Session.class);
		final List<Reporte> listaReporteSP = new ArrayList<Reporte>();
		session.doWork(new Work() {

			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				ResultSet resultset = null;

				try {

					callableStatement = connection.prepareCall(Constantes.SP_RPRT_TP_PST_CLSFCCN_ENT);
					callableStatement.setString("E_ENTIDAD_ID", entidadId);
					callableStatement.setString("E_CLASIFICACION_ENTIDAD", clasificacionEntidad);
					callableStatement.setString("E_TIPO_PUESTO", tipoPuesto);
					callableStatement.setString("E_CENSO_ID", censoId);
					callableStatement.registerOutParameter("S_CURSOR", OracleTypes.CURSOR);
					callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
					callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
					callableStatement.execute();
					log.info("Se ejecuto exitosamente el SP " + Constantes.SP_RPRT_TP_PST_CLSFCCN_ENT);
					resultset = (ResultSet) callableStatement.getObject("S_CURSOR");//Cursor
					while (resultset.next()) {
						Reporte reporte = new Reporte();
						reporte.setEjeY(resultset.getString(1));
						reporte.setEjeX(resultset.getString(2));
						reporte.setCantidad(resultset.getLong(3));
						listaReporteSP.add(reporte);
					}

				} finally {
					Utilitario.cerrarConexiones(resultset, callableStatement);
				}
			}

		});

		return listaReporteSP;
	}
	
	@Override
	public List<Reporte> reporteRatioxClasificacion(final String entidadId,
			final String clasificacionEntidad,final String censoId) {
		
		Session session = entityManager.unwrap(Session.class);
		final List<Reporte> listaReporteSP = new ArrayList<Reporte>();
		session.doWork(new Work() {

			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				ResultSet resultset = null;

				try {
					callableStatement = connection.prepareCall(Constantes.SP_RPRT_RATIO_CLSFCCN_ENT);
					callableStatement.setString("E_ENTIDAD_ID", entidadId);
					callableStatement.setString("E_CLASIFICACION_ENTIDAD", clasificacionEntidad);
					callableStatement.setString("E_CENSO_ID", censoId);
					callableStatement.registerOutParameter("S_CURSOR", OracleTypes.CURSOR);
					callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
					callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
					callableStatement.execute();
					log.info("Se ejecuto exitosamente el SP " + Constantes.SP_RPRT_RATIO_CLSFCCN_ENT);
					resultset = (ResultSet) callableStatement.getObject("S_CURSOR");//Cursor
					while (resultset.next()) {
						Reporte reporte = new Reporte();
						reporte.setEjeY(resultset.getString(1));
						reporte.setCantidadDecimal(resultset.getDouble(2));
						listaReporteSP.add(reporte);
					}

				} finally {
					Utilitario.cerrarConexiones(resultset, callableStatement);
				}
			}

		});

		return listaReporteSP;
	}
	
	@Override
	public List<Reporte> reporteTipoEntidadxClasificacion(final String entidadId,
			final String clasificacionEntidad,final String tipoEntidad,final String censoId) {
		
		Session session = entityManager.unwrap(Session.class);
		final List<Reporte> listaReporteSP = new ArrayList<Reporte>();
		session.doWork(new Work() {

			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				ResultSet resultset = null;

				try {

					callableStatement = connection.prepareCall(Constantes.SP_RPRT_TP_ENT_CLSFCCN_ENT);
					callableStatement.setString("E_ENTIDAD_ID", entidadId);
					callableStatement.setString("E_CLASIFICACION_ENTIDAD", clasificacionEntidad);
					callableStatement.setString("E_TIPO_ENTIDAD", tipoEntidad);
					callableStatement.setString("E_CENSO_ID", censoId);
					callableStatement.registerOutParameter("S_CURSOR", OracleTypes.CURSOR);
					callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
					callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
					callableStatement.execute();
					log.info("Se ejecuto exitosamente el SP " + Constantes.SP_RPRT_TP_ENT_CLSFCCN_ENT);
					resultset = (ResultSet) callableStatement.getObject("S_CURSOR");//Cursor
					while (resultset.next()) {
						Reporte reporte = new Reporte();
						reporte.setEjeY(resultset.getString(1));
						reporte.setEjeX(resultset.getString(2));
						reporte.setCantidad(resultset.getLong(3));
						listaReporteSP.add(reporte);
					}

				} finally {
					Utilitario.cerrarConexiones(resultset, callableStatement);
				}
			}

		});

		return listaReporteSP;
	}
	
	@Override
	public List<Reporte> reporteTipoEntidadxEntidad(final String censoId) {
		
		Session session = entityManager.unwrap(Session.class);
		final List<Reporte> listaReporteSP = new ArrayList<Reporte>();
		session.doWork(new Work() {

			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				ResultSet resultset = null;

				try {

					callableStatement = connection.prepareCall(Constantes.SP_RPRT_ENT_TP_ENT);
					callableStatement.setString("E_CENSO_ID", censoId);
					callableStatement.registerOutParameter("S_CURSOR", OracleTypes.CURSOR);
					callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
					callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
					callableStatement.execute();
					log.info("Se ejecuto exitosamente el SP " + Constantes.SP_RPRT_ENT_TP_ENT);
					resultset = (ResultSet) callableStatement.getObject("S_CURSOR");//Cursor
					while (resultset.next()) {
						Reporte reporte = new Reporte();
						reporte.setEjeY(resultset.getString(1));
						reporte.setCantidad(resultset.getLong(2));
						listaReporteSP.add(reporte);
					}

				} finally {
					Utilitario.cerrarConexiones(resultset, callableStatement);
				}
			}

		});

		return listaReporteSP;
	}
	
	@Override
	public List<Reporte> reporteProgresoDirectivoResponsable(final String tipo,
						final String entidadId,final String censoId) {
		
		Session session = entityManager.unwrap(Session.class);
		final List<Reporte> listaReporteSP = new ArrayList<Reporte>();
		session.doWork(new Work() {

			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				ResultSet resultset = null;

				try {

					callableStatement = connection.prepareCall(Constantes.SP_RPRT_PROGRESS_CENSO_ENT);
					callableStatement.setString("E_TIPO", tipo);
					callableStatement.setString("E_ENTIDAD_ID", entidadId);
					callableStatement.setString("E_CENSO_ID", censoId);
					callableStatement.registerOutParameter("S_CURSOR", OracleTypes.CURSOR);
					callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
					callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
					callableStatement.execute();
					log.info("Se ejecuto exitosamente el SP.");
					resultset = (ResultSet) callableStatement.getObject("S_CURSOR");//Cursor
					while (resultset.next()) {
						Reporte reporte = new Reporte();						
						reporte.setCantidad(resultset.getLong(1));
						listaReporteSP.add(reporte);
					}

				} finally {
					Utilitario.cerrarConexiones(resultset, callableStatement);
				}
			}

		});

		return listaReporteSP;
	}
    
	@Override
	public List<Reporte> reporteAvanceDirectivoEntidad(final String tipo,final String entidadId,final String censoId) {
		
		Session session = entityManager.unwrap(Session.class);
		final List<Reporte> listaReporteSP = new ArrayList<Reporte>();
		session.doWork(new Work() {

			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				ResultSet resultset = null;

				try {

					callableStatement = connection.prepareCall(Constantes.SP_RPRT_AVANCE_ENTI);		
					callableStatement.setString("E_TIPO", tipo);
					callableStatement.setString("E_ENTIDAD_ID", entidadId);
					callableStatement.setString("E_CENSO_ID", censoId);
					callableStatement.registerOutParameter("S_CURSOR", OracleTypes.CURSOR);
					callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
					callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
					callableStatement.execute();
					log.info("Se ejecuto exitosamente el SP.");
					resultset = (ResultSet) callableStatement.getObject("S_CURSOR");//Cursor
					while (resultset.next()) {
						Reporte reporte = new Reporte();			
						reporte.setEjeY(resultset.getString(1));
						reporte.setCantidad(resultset.getLong(2));
						reporte.setCantidadSerie2(resultset.getLong(3));
						listaReporteSP.add(reporte);
					}

				} finally {
					Utilitario.cerrarConexiones(resultset, callableStatement);
				}
			}

		});

		return listaReporteSP;
	}
	
	
	@Override
	public List<Reporte> reporteTrazabilidadDirectivo(final String tipo,final String entidadId,final String censoId) {
		
		Session session = entityManager.unwrap(Session.class);
		final List<Reporte> listaReporteSP = new ArrayList<Reporte>();
		session.doWork(new Work() {

			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				ResultSet resultset = null;

				try {

					callableStatement = connection.prepareCall(Constantes.SP_RPRT_TRAZA_DIRECTIVO);	
					callableStatement.setString("E_TIPO", tipo);
					callableStatement.setString("E_ENTIDAD_ID", entidadId);
					callableStatement.setString("E_CENSO_ID", censoId);
					callableStatement.registerOutParameter("S_CURSOR", OracleTypes.CURSOR);
					callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
					callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
					callableStatement.execute();
					log.info("Se ejecuto exitosamente el SP.");
					resultset = (ResultSet) callableStatement.getObject("S_CURSOR");//Cursor
					while (resultset.next()) {
						Reporte reporte = new Reporte();			
						reporte.setEjeY(resultset.getString(1));
						reporte.setCantidad(resultset.getLong(2));						
						listaReporteSP.add(reporte);
					}

				} finally {
					Utilitario.cerrarConexiones(resultset, callableStatement);
				}
			}

		});

		return listaReporteSP;
	}
	
	@Override
	public List<Reporte> reportePendienteFinalizado(final String tipo,final String entidadId,final String censoId) {
		
		Session session = entityManager.unwrap(Session.class);
		final List<Reporte> listaReporteSP = new ArrayList<Reporte>();
		session.doWork(new Work() {

			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				ResultSet resultset = null;

				try {

					callableStatement = connection.prepareCall(Constantes.SP_RPRT_PEN_FIN);		
					callableStatement.setString("E_TIPO", tipo);
					callableStatement.setString("E_ENTIDAD_ID", entidadId);
					callableStatement.setString("E_CENSO_ID", censoId);
					callableStatement.registerOutParameter("S_CURSOR", OracleTypes.CURSOR);
					callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
					callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
					callableStatement.execute();
					log.info("Se ejecuto exitosamente el SP.");
					resultset = (ResultSet) callableStatement.getObject("S_CURSOR");//Cursor
					while (resultset.next()) {
						Reporte reporte = new Reporte();			
						reporte.setEjeY(resultset.getString(1));
						reporte.setCantidad(resultset.getLong(2));						
						listaReporteSP.add(reporte);
					}

				} finally {
					Utilitario.cerrarConexiones(resultset, callableStatement);
				}
			}

		});

		return listaReporteSP;
	}
	
	@Override
	public List<Responsable> buscarResponsablesNotificar(final Integer censoId,final Integer entidadId) throws DAOExcepcion {
		log.info("Parametro entrada [censoId] " + censoId);
		log.info("Parametro entrada [entidadId] " + entidadId);
		Session session = entityManager.unwrap(Session.class);
		final List<Responsable> listaResponsable = new ArrayList<Responsable>();
		final MensajeRespuesta mensajeRespuesta = new MensajeRespuesta();
		try {
			session.doWork(new Work() {
				@Override
				public void execute(Connection connection) throws SQLException  {
					CallableStatement callableStatement = null;
					ResultSet resultset = null;
					
					try {
						connection.setAutoCommit(false);
						callableStatement = connection.prepareCall(Constantes.SP_NOTIFICAR_RESPONSABLES);
						callableStatement.setLong("E_CENSO_ID", censoId);
						callableStatement.setLong("E_ENTIDAD_ID", entidadId);
						callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
						callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
						callableStatement.registerOutParameter("S_DES_ERROR_INFO", OracleTypes.VARCHAR);
						callableStatement.execute();
						log.info("Se ejecuto exitosamente el SP.");
						int codigoError = callableStatement.getInt("S_ID_ERROR");
						String mensajeError = callableStatement.getString("S_DES_ERROR");
						String mensajeInfo = callableStatement.getString("S_DES_ERROR_INFO");
	
						mensajeRespuesta.setCodigoError(String.valueOf(codigoError));
						mensajeRespuesta.setMensajeError(mensajeError);
						mensajeRespuesta.setMensajeInfo(mensajeInfo);
	
						if( codigoError == 0){
							log.info("Se registro datos con exito en BD.");
							connection.commit();
						} else {
							log.info("No se pudo registrar los datos en BD. " + mensajeInfo);
							throw new SQLException();
						}
					} catch(SQLException e){
						if (mensajeRespuesta.getMensajeInfo() != null){
							log.error("SQLException : " + mensajeRespuesta.getMensajeInfo());
						}
						connection.rollback();
						throw e;
					} catch (Exception e) {
						log.error("Exception : ", e);
						connection.rollback();
						throw e; 
					} finally {
						Utilitario.cerrarConexiones(resultset, callableStatement);
					}
				}
			});
		
		} catch (Exception e) {
			if (mensajeRespuesta.getCodigoError() == null) {
				String mensajeError = consumirProcedimientoDAOLocal.consultarMensaje(Constantes.CODIGO_MSJ_ERROR_DB);
				throw new DAOExcepcion(Constantes.CODIGO_MSJ_ERROR_DB, mensajeError);
			}else{
				throw new DAOExcepcion(mensajeRespuesta.getCodigoError(), mensajeRespuesta.getMensajeError());
			}
		}
		return listaResponsable;
	}
	
	@Override
	public List<DirectivoPublico> buscarDirectivosNotificar(final Integer censoId,final Integer entidadId) throws DAOExcepcion {
		Session session = entityManager.unwrap(Session.class);
		final List<DirectivoPublico> listaDirectivoPublico = new ArrayList<DirectivoPublico>();
		final MensajeRespuesta mensajeRespuesta = new MensajeRespuesta();
		try {
			session.doWork(new Work() {
				@Override
				public void execute(Connection connection) throws SQLException  {
					CallableStatement callableStatement = null;
					ResultSet resultset = null;
					
					try {
						connection.setAutoCommit(false);
						callableStatement = connection.prepareCall(Constantes.SP_NOTIFICAR_DIRECTIVOS);
						callableStatement.setLong("E_CENSO_ID", censoId);
						callableStatement.setLong("E_ENTIDAD_ID", entidadId);
						callableStatement.registerOutParameter("S_ID_ERROR", OracleTypes.INTEGER);
						callableStatement.registerOutParameter("S_DES_ERROR", OracleTypes.VARCHAR);
						callableStatement.registerOutParameter("S_DES_ERROR_INFO", OracleTypes.VARCHAR);
						callableStatement.execute();
						log.info("Se ejecuto exitosamente el SP.");
						int codigoError = callableStatement.getInt("S_ID_ERROR");
						String mensajeError = callableStatement.getString("S_DES_ERROR");
						String mensajeInfo = callableStatement.getString("S_DES_ERROR_INFO");
	
						mensajeRespuesta.setCodigoError(String.valueOf(codigoError));
						mensajeRespuesta.setMensajeError(mensajeError);
						mensajeRespuesta.setMensajeInfo(mensajeInfo);
	
						if( codigoError == 0){
							log.info("Se registro datos con exito en BD.");
							connection.commit();
						} else {
							log.info("No se pudo registrar los datos en BD. " + mensajeInfo);
							throw new SQLException();
						}
					} catch(SQLException e){
						if (mensajeRespuesta.getMensajeInfo() != null){
							log.error("SQLException : " + mensajeRespuesta.getMensajeInfo());
						}
						connection.rollback();
						throw e;
					} catch (Exception e) {
						log.error("Exception : ", e);
						connection.rollback();
						throw e; 
					} finally {
						Utilitario.cerrarConexiones(resultset, callableStatement);
					}
				}
			});
		
		} catch (Exception e) {
			if (mensajeRespuesta.getCodigoError() == null) {
				String mensajeError = consumirProcedimientoDAOLocal.consultarMensaje(Constantes.CODIGO_MSJ_ERROR_DB);
				throw new DAOExcepcion(Constantes.CODIGO_MSJ_ERROR_DB, mensajeError);
			}else{
				throw new DAOExcepcion(mensajeRespuesta.getCodigoError(), mensajeRespuesta.getMensajeError());
			}
		}
		return listaDirectivoPublico;
	}

}
